using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 闸机记录查询工具 - 查询学生进出闸机的记录，支持分表查询和聚合统计
/// </summary>
public class GetGateRecordsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetGateRecordsTool> _logger;

    public string Name => "get_gate_records";
    public string Description => "查询学生进出闸机记录，包括宿舍、图书馆、校门等。支持按学生姓名、时间范围筛选，支持统计次数。";

    public GetGateRecordsTool(
        IDatabaseExecutor executor,
        ILogger<GetGateRecordsTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        try
        {
            // 获取参数
            string? studentName = parameters.ContainsKey("studentName") ? parameters["studentName"]?.ToString() : null;
            string? startTime = parameters.ContainsKey("startTime") ? parameters["startTime"]?.ToString() : null;
            string? endTime = parameters.ContainsKey("endTime") ? parameters["endTime"]?.ToString() : null;
            int gateType = parameters.ContainsKey("gateType") ? Convert.ToInt32(parameters["gateType"]) : 2; // 默认学生公寓
            bool countOnly = parameters.ContainsKey("countOnly") && Convert.ToBoolean(parameters["countOnly"]);

            // 确定需要查询的表（支持分表）
            // 注意：不需要规范化时间，GetTableNames 会使用 ParseDateTime 直接处理
            var tables = GetTableNames(startTime, endTime);

            if (tables.Count == 0)
            {
                return new ToolResult
                {
                    Success = false,
                    Errors = new List<string> { "无法确定查询的表，请提供时间范围" }
                };
            }

            // 构建SQL
            var sql = BuildQuery(tables, studentName, startTime, endTime, gateType, countOnly);

            _logger.LogInformation("Executing gate records query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "闸机记录查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["tables"] = string.Join(", ", tables)
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing gate records query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询错误: {ex.Message}" }
            };
        }
    }

    private string BuildQuery(List<string> tables, string? studentName, string? startTime, string? endTime, int gateType, bool countOnly)
    {
        var sb = new StringBuilder();

        // 单表查询 - 直接查询，不使用 UNION ALL
        if (tables.Count == 1)
        {
            if (countOnly)
            {
                // 统计查询
                sb.AppendLine("SELECT");
                sb.AppendLine("    gi.XGH AS 学工号,");
                sb.AppendLine("    gi.XM AS 姓名,");
                sb.AppendLine("    COUNT(*) AS 通过次数,");
                sb.AppendLine("    MIN(gi.SJ) AS 首次通过时间,");
                sb.AppendLine("    MAX(gi.SJ) AS 最后通过时间");
            }
            else
            {
                // 详细记录查询
                sb.AppendLine("SELECT");
                sb.AppendLine("    gi.XGH AS 学工号,");
                sb.AppendLine("    gi.XM AS 姓名,");
                sb.AppendLine("    DATE_FORMAT(gi.SJ, '%Y-%m-%d %H:%i:%s') AS 通过时间,");
                sb.AppendLine("    gi.XQMC AS 校区,");
                sb.AppendLine("    gi.ZJMC AS 闸机名称,");
                sb.AppendLine("    gd.GateLocation AS 闸机位置,");
                sb.AppendLine("    gi.CRBS AS 出入标识,");
                sb.AppendLine("    gi.ZTMC AS 通过状态,");
                sb.AppendLine("    CASE gd.GateType");
                sb.AppendLine("        WHEN 1 THEN '校门口'");
                sb.AppendLine("        WHEN 2 THEN '学生公寓'");
                sb.AppendLine("        WHEN 3 THEN '图书馆'");
                sb.AppendLine("        WHEN 4 THEN '办公区'");
                sb.AppendLine("        ELSE '其他'");
                sb.AppendLine("    END AS 闸机类型");
            }

            sb.AppendLine($"FROM {tables[0]} gi");
            sb.AppendLine("LEFT JOIN TblGateDetail gd ON gi.ZJID = gd.GateCode");
            sb.AppendLine($"WHERE gd.GateType = {gateType}");

            if (!string.IsNullOrEmpty(studentName))
            {
                sb.AppendLine($"    AND gi.XM = '{studentName}'");
            }

            if (countOnly)
            {
                sb.AppendLine("GROUP BY gi.XGH, gi.XM");
                sb.AppendLine("HAVING COUNT(*) > 5");
                sb.AppendLine("ORDER BY 通过次数 DESC");
            }
            else
            {
                sb.AppendLine("ORDER BY gi.SJ DESC");
            }

            sb.AppendLine("LIMIT 1000");
        }
        else
        {
            // 多表查询 - 使用 UNION ALL
            sb.AppendLine("SELECT * FROM (");

            for (int i = 0; i < tables.Count; i++)
            {
                if (i > 0)
                {
                    sb.AppendLine("UNION ALL");
                }

                if (countOnly)
                {
                    sb.AppendLine("SELECT");
                    sb.AppendLine("    gi.XGH AS 学工号,");
                    sb.AppendLine("    gi.XM AS 姓名,");
                    sb.AppendLine("    COUNT(*) AS 通过次数,");
                    sb.AppendLine("    MIN(gi.SJ) AS 首次通过时间,");
                    sb.AppendLine("    MAX(gi.SJ) AS 最后通过时间");
                }
                else
                {
                    sb.AppendLine("SELECT");
                    sb.AppendLine("    gi.XGH AS 学工号,");
                    sb.AppendLine("    gi.XM AS 姓名,");
                    sb.AppendLine("    DATE_FORMAT(gi.SJ, '%Y-%m-%d %H:%i:%s') AS 通过时间,");
                    sb.AppendLine("    gi.XQMC AS 校区,");
                    sb.AppendLine("    gi.ZJMC AS 闸机名称,");
                    sb.AppendLine("    gd.GateLocation AS 闸机位置,");
                    sb.AppendLine("    gi.CRBS AS 出入标识,");
                    sb.AppendLine("    gi.ZTMC AS 通过状态,");
                    sb.AppendLine("    CASE gd.GateType");
                    sb.AppendLine("        WHEN 1 THEN '校门口'");
                    sb.AppendLine("        WHEN 2 THEN '学生公寓'");
                    sb.AppendLine("        WHEN 3 THEN '图书馆'");
                    sb.AppendLine("        WHEN 4 THEN '办公区'");
                    sb.AppendLine("        ELSE '其他'");
                    sb.AppendLine("    END AS 闸机类型");
                }

                sb.AppendLine($"FROM {tables[i]} gi");
                sb.AppendLine("LEFT JOIN TblGateDetail gd ON gi.ZJID = gd.GateCode");
                sb.AppendLine($"WHERE gd.GateType = {gateType}");

                if (!string.IsNullOrEmpty(studentName))
                {
                    sb.AppendLine($"    AND gi.XM = '{studentName}'");
                }

                if (countOnly)
                {
                    sb.AppendLine("GROUP BY gi.XGH, gi.XM");
                }
            }

            sb.AppendLine(") AS all_records");

            if (countOnly)
            {
                sb.AppendLine("GROUP BY 学工号, 姓名");
                sb.AppendLine("HAVING SUM(通过次数) > 5");
                sb.AppendLine("ORDER BY SUM(通过次数) DESC");
            }
            else
            {
                sb.AppendLine("ORDER BY 通过时间 DESC");
            }

            sb.AppendLine("LIMIT 1000");
        }

        return sb.ToString();
    }

    /// <summary>
    /// 根据时间范围确定需要查询的表名
    /// </summary>
    private List<string> GetTableNames(string? startTime, string? endTime)
    {
        var tables = new List<string>();

        if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
        {
            // 没有时间范围，查询当前月份
            var currentMonth = DateTime.Now.ToString("yyyyMM");
            tables.Add($"tblgateinformation_{currentMonth}01");
            return tables;
        }

        DateTime start, end;

        // 增强的时间解析：支持只有月份的情况
        start = ParseDateTime(startTime, true);
        end = ParseDateTime(endTime, false);

        // 特殊处理：如果只提供了一个时间参数，且该参数是月份描述
        // 那么查询范围应该是该月的整个月份，而不是从该月到当前时间
        if (!string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
        {
            // 检查是否是月份描述（中文月份或纯数字月份）
            if (IsMonthOnlyInput(startTime))
            {
                // 设置结束时间为该月的最后一天
                end = new DateTime(start.Year, start.Month, DateTime.DaysInMonth(start.Year, start.Month));
            }
        }
        else if (string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
        {
            // 只提供了结束时间
            if (IsMonthOnlyInput(endTime))
            {
                // 设置开始时间为该月的第一天
                start = new DateTime(end.Year, end.Month, 1);
            }
        }

        // 生成所有涉及的月份表
        var current = new DateTime(start.Year, start.Month, 1);
        var endMonth = new DateTime(end.Year, end.Month, 1);

        while (current <= endMonth)
        {
            var tableName = $"tblgateinformation_{current:yyyyMM}01";
            tables.Add(tableName);
            current = current.AddMonths(1);
        }

        return tables;
    }

    /// <summary>
    /// 判断输入是否为纯月份描述
    /// </summary>
    private bool IsMonthOnlyInput(string input)
    {
        if (string.IsNullOrEmpty(input))
            return false;

        input = input.Trim();

        // YYYY-MM 格式（如 "2025-09"）
        if (System.Text.RegularExpressions.Regex.IsMatch(input, @"^\d{4}-\d{1,2}$"))
            return true;

        // 中文月份
        var chineseMonths = new[] { "一月", "二月", "三月", "四月", "五月", "六月",
                                     "七月", "八月", "九月", "十月", "十一月", "十二月" };
        if (chineseMonths.Any(m => input.Contains(m)))
            return true;

        // 纯数字月份（1-12）
        if (int.TryParse(input, out var month) && month >= 1 && month <= 12)
            return true;

        // YYYYMM 格式
        if (input.Length == 6 && int.TryParse(input, out _))
            return true;

        return false;
    }

    /// <summary>
    /// 解析日期时间，支持多种格式
    /// </summary>
    private DateTime ParseDateTime(string? dateTimeStr, bool isStart)
    {
        if (string.IsNullOrEmpty(dateTimeStr))
        {
            return isStart ? DateTime.Now.AddDays(-7) : DateTime.Now;
        }

        dateTimeStr = dateTimeStr.Trim();

        // 处理 YYYY-MM 格式（如 "2025-09"）- 优先处理，避免被 DateTime.TryParse 错误解析
        if (System.Text.RegularExpressions.Regex.IsMatch(dateTimeStr, @"^\d{4}-\d{1,2}$"))
        {
            var parts = dateTimeStr.Split('-');
            var yearPart = int.Parse(parts[0]);
            var monthPart = int.Parse(parts[1]);
            if (monthPart >= 1 && monthPart <= 12)
            {
                return new DateTime(yearPart, monthPart, isStart ? 1 : DateTime.DaysInMonth(yearPart, monthPart));
            }
        }

        // 处理中文月份描述（如 "九月份"、"9月"）
        var chineseMonths = new Dictionary<string, int>
        {
            ["一月"] = 1, ["二月"] = 2, ["三月"] = 3, ["四月"] = 4,
            ["五月"] = 5, ["六月"] = 6, ["七月"] = 7, ["八月"] = 8,
            ["九月"] = 9, ["十月"] = 10, ["十一月"] = 11, ["十二月"] = 12
        };

        foreach (var kvp in chineseMonths)
        {
            if (dateTimeStr.Contains(kvp.Key))
            {
                var year = DateTime.Now.Year;
                return new DateTime(year, kvp.Value, isStart ? 1 : DateTime.DaysInMonth(year, kvp.Value));
            }
        }

        // 尝试标准解析（如 "2025-09-01"、"2025-09-01 12:00:00"）
        if (DateTime.TryParse(dateTimeStr, out var result))
        {
            return result;
        }

        // 处理纯数字月份格式（如 "9" 或 "09"）
        if (int.TryParse(dateTimeStr, out var month) && month >= 1 && month <= 12)
        {
            var year = DateTime.Now.Year; // 默认当前年份
            return new DateTime(year, month, isStart ? 1 : DateTime.DaysInMonth(year, month));
        }

        // 处理 YYYYMM 格式（如 "202509"）
        if (dateTimeStr.Length == 6 && int.TryParse(dateTimeStr, out _))
        {
            var year = int.Parse(dateTimeStr.Substring(0, 4));
            var monthPart = int.Parse(dateTimeStr.Substring(4, 2));
            if (monthPart >= 1 && monthPart <= 12)
            {
                return new DateTime(year, monthPart, isStart ? 1 : DateTime.DaysInMonth(year, monthPart));
            }
        }

        // 默认返回当前时间的前一周或当前时间
        return isStart ? DateTime.Now.AddDays(-7) : DateTime.Now;
    }

    /// <summary>
    /// 规范化日期时间字符串
    /// </summary>
    private string NormalizeDateTime(string dateTimeStr, bool isStart)
    {
        dateTimeStr = dateTimeStr.Trim();

        // 如果已经包含时间部分，直接返回
        if (dateTimeStr.Contains(":"))
        {
            return dateTimeStr;
        }

        // 只有日期，补充时间
        if (isStart)
        {
            return $"{dateTimeStr} 00:00:00";
        }
        else
        {
            if (DateTime.TryParse(dateTimeStr, out var date))
            {
                var nextDay = date.AddDays(1);
                return nextDay.ToString("yyyy-MM-dd HH:mm:ss");
            }
            return $"{dateTimeStr} 23:59:59";
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        // 所有参数都是可选的
        return true;
    }
}
